package com.app.util;

import com.app.entity.ColumnEntity;
import com.app.entity.ExcelRowData;
import com.app.entity.TemplateEntity;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

import static com.app.util.Constant.*;

public class DatabaseUtil {

    private final static Logger LOGGER = LoggerFactory.getLogger(DatabaseUtil.class);



    static {
        try {
            Class.forName(DRIVER);
        } catch (ClassNotFoundException e) {
            LOGGER.error("can not load jdbc driver", e);
        }
    }

    /**
     * 获取数据库连接
     *
     * @return
     */
    public static Connection getConnection() {
        Connection conn = null;
        try {
            conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
        } catch (SQLException e) {
            LOGGER.error("get connection failure", e);
            e.printStackTrace();
        }
        return conn;
    }

    /**
     * 关闭数据库连接
     *
     * @param conn
     */
    public static void closeConnection(Connection conn) {
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                LOGGER.error("close connection failure", e);
            }
        }
    }

    /**
     * 获取数据库下的所有表名
     */
    public static List<String> getTableNames() {
        List<String> tableNames = new ArrayList<>();
        Connection conn = getConnection();
        ResultSet rs = null;
        try {
            //获取数据库的元数据
            DatabaseMetaData db = conn.getMetaData();
            //从元数据中获取到所有的表名
            rs = db.getTables(null, null, null, new String[]{"TABLE"});
            while (rs.next()) {
                tableNames.add(rs.getString(3));
            }
        } catch (SQLException e) {
            LOGGER.error("getTableNames failure", e);
        } finally {
            try {
                rs.close();
                closeConnection(conn);
            } catch (SQLException e) {
                LOGGER.error("close ResultSet failure", e);
            }
        }
        return tableNames;
    }

    /**
     * 获取表中所有字段名称
     *
     * @param tableName 表名
     * @return
     */
    public static List<String> getColumnNames(String tableName) {
        List<String> columnNames = new ArrayList<>();
        //与数据库的连接
        Connection conn = getConnection();
        PreparedStatement pStemt = null;
        String tableSql = SQL + tableName;
        try {
            pStemt = conn.prepareStatement(tableSql);
            //结果集元数据
            ResultSetMetaData rsmd = pStemt.getMetaData();
            //表列数
            int size = rsmd.getColumnCount();
            for (int i = 0; i < size; i++) {
                columnNames.add(rsmd.getColumnName(i + 1));
            }
        } catch (SQLException e) {
            LOGGER.error("getColumnNames failure", e);
        } finally {
            if (pStemt != null) {
                try {
                    pStemt.close();
                    closeConnection(conn);
                } catch (SQLException e) {
                    LOGGER.error("getColumnNames close pstem and connection failure", e);
                }
            }
        }
        return columnNames;
    }

    /**
     * 获取表中所有字段类型
     *
     * @param tableName
     * @return
     */
    public static List<String> getColumnTypes(String tableName) {
        List<String> columnTypes = new ArrayList<>();
        //与数据库的连接
        Connection conn = getConnection();
        PreparedStatement pStemt = null;
        String tableSql = SQL + tableName;
        try {
            pStemt = conn.prepareStatement(tableSql);
            //结果集元数据
            ResultSetMetaData rsmd = pStemt.getMetaData();
            //表列数
            int size = rsmd.getColumnCount();
            for (int i = 0; i < size; i++) {
                columnTypes.add(rsmd.getColumnTypeName(i + 1));
            }
        } catch (SQLException e) {
            LOGGER.error("getColumnTypes failure", e);
        } finally {
            if (pStemt != null) {
                try {
                    pStemt.close();
                    closeConnection(conn);
                } catch (SQLException e) {
                    LOGGER.error("getColumnTypes close pstem and connection failure", e);
                }
            }
        }
        return columnTypes;
    }

    /**
     * 获取表中字段的所有注释
     *
     * @param tableName
     * @return
     */
    public static List<String> getColumnComments(String tableName) {
        List<String> columnTypes = new ArrayList<>();
        //与数据库的连接
        Connection conn = getConnection();
        PreparedStatement pStemt = null;
        String tableSql = SQL + tableName;
        List<String> columnComments = new ArrayList<>();//列名注释集合
        ResultSet rs = null;
        try {
            pStemt = conn.prepareStatement(tableSql);
            rs = pStemt.executeQuery("show full columns from " + tableName);
            while (rs.next()) {
                columnComments.add(rs.getString("Comment"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                    closeConnection(conn);
                } catch (SQLException e) {
                    LOGGER.error("getColumnComments close ResultSet and connection failure", e);
                }
            }
        }
        return columnComments;
    }

    public static void main(String[] args) {
        List<String> tableNames = getTableNames();
        System.out.println("tableNames:" + tableNames);
        for (String tableName : tableNames) {
            System.out.println("ColumnNames:" + getColumnNames(tableName));
            System.out.println("ColumnTypes:" + getColumnTypes(tableName));
            System.out.println("ColumnComments:" + getColumnComments(tableName));
        }
    }

    public static void setColumnEntityList(List<ExcelRowData> excelRowDataList, TemplateEntity templateEntity) {
        List<ColumnEntity> columnEntitys = new ArrayList<>();
        templateEntity.setColumnEntitys(columnEntitys);
        List<String> tableNames = excelRowDataList.stream().map(ExcelRowData::getTableName).distinct().collect(Collectors.toList());
        Map<String, ResultSetMetaData> resultSetMetaDataMap = new HashMap<>();
        for (int i = 0; i < tableNames.size(); i++) {
            String tableName = tableNames.get(i);
            if (StringUtils.isBlank(tableName)) {
                continue;
            }
            Connection conn = getConnection();
            PreparedStatement pStemt = null;
            String tableSql = SQL + tableName;
            try {
                pStemt = conn.prepareStatement(tableSql);
                //结果集元数据
                ResultSetMetaData rsmd = pStemt.getMetaData();
                resultSetMetaDataMap.put(tableName, rsmd);
            } catch (SQLException e) {
                LOGGER.error("getColumnNames failure", e);
            } finally {
//                if (pStemt != null) {
//                    try {
//                        pStemt.close();
//                        closeConnection(conn);
//                    } catch (SQLException e) {
//                        LOGGER.error("getColumnNames close pstem and connection failure", e);
//                    }
//                }
            }
        }
        try {
            for (int i = 0; i < excelRowDataList.size(); i++) {
                ExcelRowData excelRowData = excelRowDataList.get(i);
                if (StringUtils.isNotBlank(excelRowData.getTableName())) {
                    ResultSetMetaData rsmd = resultSetMetaDataMap.get(excelRowData.getTableName());
                    int size = rsmd.getColumnCount();
                    boolean hasColumn = false;
                    for (int j = 0; j < size; j++) {
                        String columnName = rsmd.getColumnName(j + 1);
                        String columnTypeName = rsmd.getColumnTypeName(j + 1);
                        int columnDisplaySize = rsmd.getColumnDisplaySize(j + 1);
                        if (excelRowData.getFieldName().equalsIgnoreCase(columnName)) {
                            ColumnEntity columnEntity = new ColumnEntity();
                            columnEntity.setExcelRowData(excelRowData);
                            columnEntity.setJdbcType(columnTypeName);
                            if (excelRowData.getMaxLength() == null) {
                                columnEntity.setMaxLength(columnDisplaySize);
                            } else {
                                columnEntity.setMaxLength(excelRowData.getMaxLength());
                            }
                            columnEntitys.add(columnEntity);
                            hasColumn = true;
                            break;
                        }
                    }
                    if (!hasColumn) {
                        throw new RuntimeException("灾难性错误！！！请联系全栈工程师陈勇！！！找不到字段：" + excelRowData.getFieldName());
                    }
                } else {
                    ColumnEntity columnEntity = new ColumnEntity();
                    if (excelRowData.getMaxLength() != null) {
                        columnEntity.setMaxLength(excelRowData.getMaxLength());
                    }
                    if (excelRowData.getFieldChineseName().contains("日期") || excelRowData.getFieldChineseName().contains("时间")) {
                        columnEntity.setJdbcType("DATETIME");
                    }
                    columnEntity.setExcelRowData(excelRowData);
                    columnEntitys.add(columnEntity);
                }

            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
        }
    }

}
